﻿using System;
using System.Collections.Generic;
using System.Text;
//using System.Data.SqlClient;
using System.Data;
using MySql.Data.MySqlClient;
using MySql.Data;

namespace InscriberDataManager
{
    public class DataBase
    {
        private string server = null;
        private string user = null;
        private string pass = null;
        private string database = null;
        private string dbcon = null;
        private MySqlConnection m_cnADONetConnection;
        private MySqlDataAdapter Ad_ms_Table;
        private MySqlCommandBuilder CmBl_ms_Table;
        

        public DataBase(string server, string user, string pass, string database)
        {
            this.server = server;
            this.user = user;
            this.pass = pass;
            this.database = database;
            dbcon = "server=" + this.server + ";uid=" + this.user + ";pwd=" + this.pass + ";database=" + this.database;
            //sqlExecute("SET NAMES UTF8");
        }

     
        public bool checkConnection()
        {
            bool chk = true;
            try
            {
                m_cnADONetConnection = new MySqlConnection(dbcon);
                m_cnADONetConnection.Open();
                m_cnADONetConnection.Close();
            }
            catch (MySqlException ex) { chk = false; }
            return chk;
        }

        public DataTable getTable(string sql)
        {
            DataTable Tb_Table = new DataTable();
            m_cnADONetConnection = new MySqlConnection(dbcon);
            Ad_ms_Table = new MySqlDataAdapter();
            CmBl_ms_Table = new MySqlCommandBuilder(Ad_ms_Table);
            Tb_Table = new DataTable();
            Ad_ms_Table.SelectCommand = new MySqlCommand(sql, m_cnADONetConnection);
            m_cnADONetConnection.Open();
            Tb_Table.Clear();
            Ad_ms_Table.Fill(Tb_Table);
            m_cnADONetConnection.Close();
            return Tb_Table;
        }

        public void sqlExecute(string sql)
        {
            m_cnADONetConnection = new MySqlConnection(dbcon);
            MySqlCommand cmd = new MySqlCommand(sql, m_cnADONetConnection);
            try
            {
                m_cnADONetConnection.Open();
                cmd.ExecuteNonQuery();
                m_cnADONetConnection.Close();
            }
            catch (MySqlException e)
            {
                Console.WriteLine("Error: {0}", e.Message);
            }

        }

        public DataTable procedure(string name)
        {

            DataTable Tb_Table = new DataTable();
            m_cnADONetConnection = new MySqlConnection(dbcon);
            Ad_ms_Table = new MySqlDataAdapter();
            CmBl_ms_Table = new MySqlCommandBuilder(Ad_ms_Table);
            Tb_Table = new DataTable();
            Ad_ms_Table.SelectCommand = new MySqlCommand(name, m_cnADONetConnection);
            Ad_ms_Table.SelectCommand.CommandType = CommandType.StoredProcedure;
            Ad_ms_Table.SelectCommand.Parameters.Clear();
            // Ad_ms_Table.SelectCommand.Parameters.Add(new SqlParameter("@pr1", SqlDbType.Int));
            // Ad_ms_Table.SelectCommand.Parameters["@pr1"].Value = encoderid;
            m_cnADONetConnection.Open();
            Tb_Table.Clear();
            Ad_ms_Table.Fill(Tb_Table);
            m_cnADONetConnection.Close();
            return Tb_Table;
        }


    }
}
